dataSPA Package:A Package for Obtaining Data for DFO Strategic Planning

Jaimie Harbin

The dataSPA package is intended to be used by Department Of Fisheries and Oceans Canada (DFO) employees to obtain data from A) the Project Planning Tool (PPT) API and B) Salary Spread sheets to plot information related to allocation of funding.

Some disclaimers/ assumptions with this package include:

For this package to work, you must be on the DFO VPN
This code assumes some employees have not changed positions over the course of their career
As of 2023/05/23, in some cases when a employment classification is not provided, it us assumed the employee was a PC-2.
The salary calculations are based off of medians of federal employee pay scales for the year of the projects fiscal year, not the exact salary of individuals. If the pay information for a specific year is not available, the closest year is found. As negotiations continue to happen, the data(salaries) built in dataset will need to be updated.
Calculated over time salary does not consider the fact sometimes overtime may be 1.5X or 2X regular salary
When a person with job classification EX is associated with a project, they are not included in total weeks or salary.
For salary predictions in plotSPA(which="salaryPredict"), overtime is not accounted for and it is assumed that each classification level worked the same amount of hours as the previous year
The PPT was only mandated in the Maritimes region 2 years ago

To install the package, you must first be a collaborator of the private repository dataSPA hosted by j-harbin (Jaimie Harbin). Once you have been granted access, run the following code to install the package:

# configure github and generate token
usethis::use_git_config(user.name = "YourName", user.email = "your@mail.com")
usethis::create_github_token() 

# paste your personal access token into pop-up that follows...
credentials::set_github_pat()

install.packages("devtools") # optional
devtools::install_github("j-harbin/dataSPA")
library(dataSPA)

Once the package is installed, the user should follow the following workflow to successfully use the package:

Figure 1: Work flow for the dataSPA package

getData()

If type is om, this function extracts data from the om-costs project planning tool (PPT) API (http://dmapps/api/ppt/om-costs/) and combines it with the fiscal year from the project_years API (http://dmapps/api/ppt/). If type is salary, this function obtains the data from the staff API (http://dmapps/api/ppt/staff/) and combines it with information from a Human Resources (HR) spreadsheet.

It should be noted that this function will only work for authorized Department of Fisheries and Oceans Canada (DFO) employees. As shown in the help documentation for this function (?getData), a cookie is required. A cookie can be obtained by DFO staff and will need to be updated every few weeks by following the steps outlined below:

1. Sign into DMApps on the internet

2. Right click in DMApps and click “Inspect Panel”.

Figure 2: Right click to find “Inspect”

3. Click the + button to the right of “Elements” and click on “Application”

Figure 3: Click >> to find “Application”

4. Under “Cookies” on the left, click on “http://dmapps” to find your csrftoken and sessionid and copy their value

5. Assign a variable in R with the following format:

cookie <- "csrftoken=YOURTOKEN; sessionid=YOURSESSIONID"

This variable cookie will then act as your cookie argument in the getData() function, e.g.:

om <- getData(type="om", cookie=cookie, keep=TRUE, path=".") # Notice keep = TRUE saves the file, and the path argument says where to save it
sal <- getData(type="salary", cookie=cookie, keep=TRUE, path=".") # Notice these are saved in the same path 
omdate <- getData(type="om_date", cookie=cookie, keep=TRUE, path=".") # Notice this is saved in the same path and done AFTER type=”om”

plotSPA()

This function plots specific graphs (determined by the which argument) using the data frames returned by getData(). It is used in the createReport() function.

The various plot types are as follows:

For which="omBar", a bar graph representing amount of money ($) coded by funding type and show the number of deliverables and milestones per year in blue and red respectively
For which="omPie", a pie chart representing amount of money ($) coded by funding type
For which="omAllocation" Line graphs representing changes in O&M investment for categories over the years for a specific project. Average rate of change per year for each category, where applicable, displayed on the graph in red
For which=omAllocationGeneral a pie chat representing proportion of #’ funding per category type is plotted for year each of the specified project
For which="salaryBar" a bar graph representing amount of salary money ($) using the median values of a specific job classification. Graph color coded by funding type indicated by the legend.
For which="salaryAllocation"Line graphs representing changes in Salary investment for different classifications over the years for a specific project. Average rate of change per year for each category, where applicable, displayed on the graph in red
For which="weekAllocation" a bar graph representing time (weeks)
For which="indeterminate" a bar graph representing percentage (%) of Indeterminate vs Non-indeterminate employees for project years.Number of staff shown on the figure in red
For which="predictSummary"a line chart showing the trends for changes in different funding scenarios
For which="predict" a bar chart showing the trends for changes in different funding scenarios is plotted with the number of stations overlying the figure with a line. A new plot is plotted for each funding scenario. If endDate is provided, the bar chart will represent funding as status quo until the end date (See example 3)
For which="predictSalary" a bar chat showing the trends of salary increasing while funding amount remains the same.Note: This plot does not include amount of overtime as that is hard to predict.
For which="predictOM a bar chart showing the trends of om increasing while funding amoutn remains the same. This plot assumes an inflation rate of 2%.
For which="overviewStatus" a pie chart is created that shows the number of projects for each of the following status’: Approved, Draft, Submitted, and Reviews for the specified parameters.

To plot the amount of money invested in O&M per funding type for the Snow crab project, for example, the code is as follows:

plotSPA(om, which="omBar", id=1093)

Figure 3 Bar graph representing amount of money ($) invested in O&M per funding type for project years 2021-2022, 2022-2023 and 2023-2024 for the snow crab project (# 1093). Graph color coded by funding type indicated by the legend.